package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import base.DbConnect;
import model.CourseSelect;
import model.TeacherCourse;

public class TeacherCourseDao {
	public static int add(String t_id, String cou_id,String cla_id) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		int res = -1;
		String sql = "insert into teacher_course values (?,?,?)";
		try {
			stmt = (PreparedStatement) conn.prepareStatement(sql);
			stmt.setString(1, t_id);
			stmt.setString(2, cou_id);
			stmt.setString(3, cla_id);
			res = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}

	public static ArrayList<TeacherCourse> select() {
		Connection conn = DbConnect.getConnect();
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<TeacherCourse> arrayList = new ArrayList<TeacherCourse>();

		String sql;
		sql = "SELECT * FROM view_teacher_course";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				TeacherCourse courseSelect = new TeacherCourse();
				courseSelect.setT_id(rs.getString("t_id"));
				courseSelect.setCou_id(rs.getString("cou_id"));
				courseSelect.setCou_name(rs.getString("cou_name"));
				courseSelect.setCla_id(rs.getString("cla_id"));
				arrayList.add(courseSelect);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return arrayList;
	}

	public static int find(String t_id, String cou_id) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql;
		sql = "SELECT * FROM teacher_course where t_id = '" + t_id + "' and " + " cou_id = '" + cou_id + "'" ;
		try {
			stmt = (PreparedStatement) conn.prepareStatement(sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return 1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}
	public static int updata(String t_id, String cou_id) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql;
		sql = "update teacher_course set t_id = ? where cou_id = ?";
		try {
			stmt = (PreparedStatement) conn.prepareStatement(sql);
			stmt.setString(1, t_id);
			stmt.setString(2, cou_id);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return 1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}

	/**
	 * 删除信息
	 * 
	 * @param department
	 * @return
	 */
	public static int delete(TeacherCourse teacherCourse) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		int res = -1;
		String sql = "DELETE from teacher_course where t_id = ? and cou_id = ? ";
		try {
			stmt = (PreparedStatement) conn.prepareStatement(sql);
			stmt.setString(1, teacherCourse.getT_id());
			stmt.setString(2, teacherCourse.getCou_id());
			res = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return res;
	}

	/**
	 * 更新信息
	 * 
	 * @param department
	 * @return
	 */
	public static int updata(TeacherCourse teacherCourse) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		int res = -1;
		try {
			String sql = "update teacher_course set t_id = ? where cou_id = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, teacherCourse.getT_id());
			stmt.setString(2, teacherCourse.getCou_id());
			res = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return res;
	}
	
	public static ArrayList<TeacherCourse> find(String t_id) {
		Connection conn = DbConnect.getConnect();
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<TeacherCourse> arrayList = new ArrayList<TeacherCourse>();

		String sql;
		sql = "SELECT * FROM view_mycourse where t_id = '" + t_id +"'";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				TeacherCourse courseSelect = new TeacherCourse();
				courseSelect.setT_id(rs.getString("t_id"));
				courseSelect.setCou_name(rs.getString("cou_name"));
				courseSelect.setCla_name(rs.getString("cla_name"));
				courseSelect.setCla_number(rs.getInt("cla_number"));
				arrayList.add(courseSelect);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return arrayList;
	}
}
